SET NOCOUNT ON

PRINT 'Resetting all procedures and triggers related to the edition/copy sub-schemas'

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_add_new_copies_for_edition' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_add_new_copies_for_edition
GO
IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_add_new_copy_for_edition' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_add_new_copy_for_edition
GO
IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_add_new_edition_for_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_add_new_edition_for_film
GO
IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_language' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_language
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_edition' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_edition
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_new_languages' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_new_languages
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_link_translated_in' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_link_translated_in
GO

IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_delete_language_orphans'
        AND type='TR')
DROP TRIGGER t_delete_language_orphans
GO

IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_delete_film_orphans'
        AND type='TR')
DROP TRIGGER t_delete_film_orphans
GO

IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_no_more_copies_left'
        AND type='TR')
DROP TRIGGER t_no_more_copies_left
GO
-------------------------------------------------------------------------------
-- Trigger to set has_no_copy on Film when there is no more copies left
-- Trigger to set to_be_deleted on Film if a deleted film is currently rented or booked
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE TRIGGER t_no_more_copies_left ON dbo.Copy
AFTER DELETE
AS
BEGIN
    DECLARE @id AS INTEGER, @edition AS INTEGER
    DECLARE @tbd AS INTEGER, @rented AS INTEGER, @booked AS INTEGER

    DECLARE copy_cursor CURSOR
    FOR
        SELECT id, id_edition, to_be_deleted, rented, booked
          FROM DELETED;

    OPEN copy_cursor;

    FETCH NEXT FROM copy_cursor INTO @id, @edition, @tbd, @rented, @booked

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF NOT EXISTS (SELECT 1
                        FROM Copy
                        WHERE Copy.id_edition IN (SELECT id
                                                    FROM Edition
                                                   WHERE id_film = (SELECT id_film
                                                                      FROM Edition
                                                                     WHERE id = @edition)))
            UPDATE Film SET has_no_copy=1 
                         WHERE isan = (SELECT id_film
                                         FROM Edition
                                        WHERE id = @edition)

        IF @rented = 1 OR @booked = 1
            BEGIN
                SET IDENTITY_INSERT Copy ON;
                SET @tbd = 1;
                INSERT INTO Copy (id, id_edition, to_be_deleted, rented, booked) VALUES (@id, @edition, @tbd, @rented, @booked)
                SET IDENTITY_INSERT Copy OFF;
            END
        FETCH NEXT FROM copy_cursor INTO @id, @edition, @tbd, @rented, @booked
    END

    CLOSE copy_cursor;
    DEALLOCATE copy_cursor;
END
GO

-------------------------------------------------------------------------------
-- Trigger to remove language orphans
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE TRIGGER t_delete_language_orphans ON dbo.Translated_In
FOR DELETE
AS
BEGIN
    DECLARE @edition AS INTEGER
    DECLARE @lname AS VARCHAR(64)

    DECLARE cursor_lang CURSOR
    FOR 
        SELECT id_edition, language_name
          FROM DELETED;

    OPEN cursor_lang;
    FETCH NEXT FROM cursor_lang INTO @edition, @lname;

    PRINT '[T_DELETE_LANGUAGE_ORPHANS] Removing orphans from Languages'
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DELETE From Language WHERE language_name = @lname;
            FETCH NEXT FROM cursor_lang INTO @edition, @lname;
        END

    CLOSE cursor_lang;
    DEALLOCATE cursor_lang;
END
GO

-------------------------------------------------------------------------------
-- Trigger to remove edition orphans
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE TRIGGER t_delete_film_orphans ON dbo.Edition
FOR DELETE
AS
BEGIN
    DECLARE @edition AS INTEGER
    DECLARE @film AS BIGINT
    DECLARE @support AS VARCHAR(64)
    DECLARE @date AS VARCHAR(64)
    DECLARE @extras AS VARCHAR(64)

    DECLARE film_cursor CURSOR
    FOR
        SELECT id, id_film, support, release_date, extras
        FROM DELETED;

    OPEN film_cursor;
    FETCH NEXT FROM Film_cursor INTO @edition, @film, @support, @date, @extras

    PRINT '[T_DELETE_LANGUAGE_ORPHANS] Removing orphans from Film'
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM Edition WHERE id_film = @film)
                DELETE From Film WHERE isan = @film;
            FETCH NEXT FROM Film_cursor INTO @edition, @film, @support, @date, @extras
        END

    CLOSE film_cursor
    DEALLOCATE film_cursor
END
GO

-------------------------------------------------------------------------------
-- Procedure to associate an edition with a lang
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_link_translated_in
    @edition         INTEGER,
    @lang_name       VARCHAR(64),
    @trans_type      VARCHAR(64)
AS
IF EXISTS (SELECT *
             FROM Translated_In
            WHERE id_edition = @edition
              AND language_name = @lang_name
              AND translation_type = @trans_type)
    BEGIN
        PRINT '[P_LINK_TRANS_IN] Error: Link between : '+CAST(@edition AS NVARCHAR(128))+' AND language : "'+@lang_name+'" of type "'+@trans_type+'".'
        RETURN -1
    END
ELSE
    BEGIN
        PRINT '[P_LINK_TRANS_IN] Linking edition: '+CAST(@edition AS NVARCHAR(128))+' AND language : "'+@lang_name+'" of type "'+@trans_type+'".'
        INSERT INTO Translated_In VALUES(@edition, @lang_name, @trans_type)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to insert a language
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_language
    @lang          AS VARCHAR(64)
AS  
    IF EXISTS (SELECT *
                FROM Language
                  WHERE language_name = @lang)
        BEGIN
            PRINT '[P_INSERT_LANGUAGE] Same lang already exists in db: '+@lang+'. Doing nothing.'
            RETURN 0
        END
    ELSE
        BEGIN
            PRINT '[P_INSERT_LANGUAGE] Inserting language '+@lang+' with success.'
            INSERT INTO Language VALUES(@lang)
            RETURN 0
        END
GO

-------------------------------------------------------------------------------
-- Procedure for adding a new edition
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_edition
    @film       AS BIGINT,
    @support    AS NVARCHAR(64),
    @date       AS SMALLDATETIME,
    @extras     AS NVARCHAR(64)
AS  
    IF EXISTS (SELECT *
                FROM Edition e
                WHERE id_film = @film
                  AND e.support = @support
                  AND e.release_date = @date
                  AND e.extras = @extras)
        BEGIN
            PRINT '[P_INSERT_EDITION] Error: Same edition already exists in db: '+CAST(@film AS NVARCHAR(128))+', '+@support+'. '
            RETURN -1
        END
    ELSE
        BEGIN
            PRINT '[P_INSERT_EDITION] Inserting an edition in database for movie : '+CAST(@film AS NVARCHAR(42))+' with success.'
            INSERT INTO Edition VALUES(@film, @support, @date, @extras)
            RETURN @@identity
        END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new awards from a charindex list
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_new_languages
    @edition     AS BIGINT,
    @string   AS NVARCHAR(4000)
AS BEGIN
    DECLARE @b INTEGER, @e INTEGER
    DECLARE @ret INTEGER

    SET @b = 1

    IF @edition IS NULL
        BEGIN
            PRINT '[P_INSERT_NEW_LANGUAGES] Error: missing edition'
            RETURN -1
        END
    IF LEN(@string) = 0
        RETURN 0

    PRINT '[P_INSERT_NEW_LANGUAGES] Adding a charindex array of Languages'
    --
    WHILE @b <> LEN(@string)
        BEGIN
            DECLARE @comma1 INTEGER
            DECLARE @lname NVARCHAR(256)
            DECLARE @ltype NVARCHAR(64)

            SET @comma1 = CHARINDEX(',', @string, @b+1)

            SET @e = CHARINDEX('|', @string, @b+1)

            SET @lname = LTRIM(SUBSTRING(@string, @b + 1, @comma1 - @b - 1))
            SET @ltype = LTRIM(SUBSTRING(@string, @comma1 + 1, @e - @comma1 - 1))

            SET @b = @e

            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM Language
                                WHERE language_name = @lname)
                    BEGIN
                        -- insert values in tables
                        EXECUTE @ret = p_insert_language @lname
                        IF (@ret < 0)
                            RETURN -1
     
                        EXECUTE @ret = p_link_translated_in @edition, @lname, @ltype
                        IF (@ret < 0)
                            RETURN -1
                    END
                ELSE
                    BEGIN
                        EXECUTE @ret = p_link_translated_in @edition, @lname, @ltype
                        IF (@ret < 0)
                            RETURN -1
                    END
            END
        END
END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new editions to a film
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_add_new_edition_for_film
    @isan       AS BIGINT,
    @support    AS VARCHAR(64),
    @date       AS SMALLDATETIME,
    @extras     AS VARCHAR(64),
    @translationString AS VARCHAR(4000)
AS BEGIN
    DECLARE @ret INTEGER
    DECLARE @edition INTEGER

    IF @isan IS NULL
        PRINT 'you can''t add an edition without giving a film'
    
    BEGIN TRANSACTION T_NEW_EDITION

    PRINT '[NEW_EDITION] Adding an edition for the movie '+CAST(@isan AS VARCHAR(24))+'...'

    EXECUTE @ret = p_insert_edition @isan, @support, @date, @extras
    IF (@ret < 0)
        GOTO P_NEW_EDITION__FAILURE
    ELSE
        SET @edition = @ret

    EXECUTE @ret = p_insert_new_languages @edition, @translationString
    IF (@ret < 0)
        GOTO P_NEW_EDITION__FAILURE

    PRINT '[NEW_EDITION] Success: changes were comitted'
    COMMIT TRANSACTION T_NEW_EDITION
    RETURN @edition

P_NEW_EDITION__FAILURE:
    PRINT '[NEW_EDITION] Failure: End of Transaction, changes aren''t comitted.'
    ROLLBACK TRANSACTION T_NEW_EDITION
    RETURN -1

END
GO

-------------------------------------------------------------------------------
-- Procedure for adding a new copy for an edition
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_add_new_copy_for_edition
    @edition       AS INTEGER
AS BEGIN
    DECLARE @copy INTEGER

    IF @edition IS NULL
        PRINT 'you can''t add a copy without giving an edition'
    
    BEGIN TRANSACTION T_NEW_COPY

    PRINT '[NEW_COPY] Adding a copy for the edition '+CAST(@edition AS VARCHAR(100))+'...'

    INSERT INTO Copy VALUES(@edition, 0, 0, 0)
    SET @copy = @@identity
    
    IF @copy = NULL
    BEGIN
        PRINT '[NEW_EDITION] Failure: End of Transaction, changes aren''t comitted.'
        ROLLBACK TRANSACTION T_NEW_EDITION
        RETURN -1
    END

    PRINT '[NEW_EDITION] Success: changes were comitted : copy is '+CAST(@copy AS VARCHAR(100))
    COMMIT TRANSACTION T_NEW_EDITION
    RETURN @copy

END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new copies to an edition
-- By: Bernard Pratz
-------------------------------------------------------------------------------
CREATE PROCEDURE p_add_new_copies_for_edition
    @edition    AS INTEGER,
    @nb         AS INTEGER
AS BEGIN
    DECLARE @i AS INTEGER

    IF @nb < 0 RETURN -1
    IF NOT EXISTS (SELECT 1 FROM Edition WHERE id = @edition) RETURN -1

    PRINT '[NEW_COPIES] Adding '+CAST(@nb AS VARCHAR(10))+' new copies for edition '+CAST(@edition AS VARCHAR(100))+'...'

    SET @i = 0
    WHILE @i < @nb
        BEGIN
            EXECUTE p_add_new_copy_for_edition @edition
            SET @i = @i+1
        END
END
GO
